﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using CommonLibrary.DB;
using CommonLibrary.DataModel.Basic;
using System.Data;

namespace ServiceApplication.Dao.Basic
{
    public class TxlDao
    {
        public int AddEntity(TxlEntity txlEntity)
        {
            List<SqlParameter> parameterList = new List<SqlParameter>();
            parameterList.Add(new SqlParameter("@txl_name", txlEntity.txl_name));
            parameterList.Add(new SqlParameter("@txl_cellphone", txlEntity.txl_cellphone));
            parameterList.Add(new SqlParameter("@txl_telephone", txlEntity.txl_telephone));
            parameterList.Add(new SqlParameter("@txl_address", txlEntity.txl_address));
            parameterList.Add(new SqlParameter("@txl_email", txlEntity.txl_email));
            parameterList.Add(new SqlParameter("@txl_QQ", txlEntity.txl_QQ));
            parameterList.Add(new SqlParameter("@txl_fax", txlEntity.txl_fax));
            parameterList.Add(new SqlParameter("@txl_dept", txlEntity.txl_dept));
            parameterList.Add(new SqlParameter("@txl_duty", txlEntity.txl_duty));
            parameterList.Add(new SqlParameter("@txl_descn", txlEntity.txl_descn));
            parameterList.Add(new SqlParameter("@txlgroup_id", txlEntity.txlgroup_id));
            parameterList.Add(new SqlParameter("@user_id", txlEntity.user_id));


            string sql = "insert into user_txl (txl_name,txl_cellphone,txl_telephone,txl_address,txl_email,txl_QQ,txl_fax,txl_dept, txl_duty,txl_descn,txlgroup_id,user_id) values " +
                " (@txl_name,@txl_cellphone,@txl_telephone,@txl_address,@txl_email,@txl_QQ,@txl_fax,@txl_dept,@txl_duty,@txl_descn,@txlgroup_id,@user_id); " +
                " select @@identity";
            string id = DBExecuteUtil.getUniqueResult(sql, parameterList.ToArray());
            return int.Parse(id);
        }

        public void DleteEntity(int txl_id)
        {
            List<SqlParameter> parameterList = new List<SqlParameter>();
            parameterList.Add(new SqlParameter("@txl_id", txl_id));
            string sql = "delete from user_txl where txl_id=@txl_id ";
            DBExecuteUtil.executeSqlWithPar(sql, parameterList.ToArray());
        }

        public void UpdateEntity(TxlEntity txlEntity)
        {
            List<SqlParameter> parameterList = new List<SqlParameter>();
            parameterList.Add(new SqlParameter("@txl_id", txlEntity.txl_id));
            parameterList.Add(new SqlParameter("@txl_name", txlEntity.txl_name));
            parameterList.Add(new SqlParameter("@txl_cellphone", txlEntity.txl_cellphone));
            parameterList.Add(new SqlParameter("@txl_telephone", txlEntity.txl_telephone));
            parameterList.Add(new SqlParameter("@txl_address", txlEntity.txl_address));
            parameterList.Add(new SqlParameter("@txl_email", txlEntity.txl_email));
            parameterList.Add(new SqlParameter("@txl_QQ", txlEntity.txl_QQ));
            parameterList.Add(new SqlParameter("@txl_fax", txlEntity.txl_fax));
            parameterList.Add(new SqlParameter("@txl_dept", txlEntity.txl_dept));
            parameterList.Add(new SqlParameter("@txl_duty", txlEntity.txl_duty));
            parameterList.Add(new SqlParameter("@txl_descn", txlEntity.txl_descn));
            parameterList.Add(new SqlParameter("@txlgroup_id", txlEntity.txlgroup_id));
            parameterList.Add(new SqlParameter("@user_id", txlEntity.user_id));

            string sql = " update user_txl set txl_name=@txl_name,txl_cellphone=@txl_cellphone,txl_telephone=@txl_telephone,txl_address=@txl_address,txl_email=@txl_email, " +
                             " txl_QQ=@txl_QQ,txl_fax=@txl_fax,txl_dept=@txl_dept, txl_duty=@txl_duty,txl_descn=@txl_descn,txlgroup_id=@txlgroup_id,user_id=@user_id where txl_id=@txl_id ";

            DBExecuteUtil.executeSqlWithPar(sql, parameterList.ToArray());
        }

        public TxlEntity QueryEntity(int txl_id)
        {
            TxlEntity txlEntity = null;
            List<SqlParameter> parameterList = new List<SqlParameter>();
            parameterList.Add(new SqlParameter("@txl_id", txl_id));
            string sql = "select txl_id,txl_name,txl_cellphone,txl_telephone,txl_address,txl_email,txl_QQ,txl_fax,txl_dept, txl_duty,txl_descn,txlgroup_id,user_id " +
                " from user_txl where txl_id=@txl_id";

            var result = DBExecuteUtil.querySqlArray(sql, parameterList.ToArray());
            if (result == null) return null;

            txlEntity = new TxlEntity();
            txlEntity.txl_id = int.Parse(result[0][0]);
            txlEntity.txl_name = result[0][1];
            txlEntity.txl_cellphone = result[0][2];
            txlEntity.txl_telephone = result[0][3];
            txlEntity.txl_address = result[0][4];
            txlEntity.txl_email = result[0][5];
            txlEntity.txl_QQ = result[0][6];
            txlEntity.txl_fax = result[0][7];
            txlEntity.txl_dept = result[0][8];
            txlEntity.txl_duty = result[0][9];
            txlEntity.txl_descn = result[0][10];
            txlEntity.txlgroup_id = int.Parse(result[0][11]);
            txlEntity.user_id = int.Parse(result[0][12]);

            return txlEntity;
        }

        public TxlEntity QueryEntity(int user_id, string txl_name)
        {
            TxlEntity txlEntity = null;
            List<SqlParameter> parameterList = new List<SqlParameter>();
            parameterList.Add(new SqlParameter("@txl_name", txl_name));
            parameterList.Add(new SqlParameter("@user_id", user_id));
            string sql = "select txl_id,txl_name,txl_cellphone,txl_telephone,txl_address,txl_email,txl_QQ,txl_fax,txl_dept, txl_duty,txl_descn,txlgroup_id,user_id " +
                " from user_txl where txl_name=@txl_name and user_id=@user_id";

            var result = DBExecuteUtil.querySqlArray(sql, parameterList.ToArray());
            if (result == null) return null;

            txlEntity = new TxlEntity();
            txlEntity.txl_id = int.Parse(result[0][0]);
            txlEntity.txl_name = result[0][1];
            txlEntity.txl_cellphone = result[0][2];
            txlEntity.txl_telephone = result[0][3];
            txlEntity.txl_address = result[0][4];
            txlEntity.txl_email = result[0][5];
            txlEntity.txl_QQ = result[0][6];
            txlEntity.txl_fax = result[0][7];
            txlEntity.txl_dept = result[0][8];
            txlEntity.txl_duty = result[0][9];
            txlEntity.txl_descn = result[0][10];
            txlEntity.txlgroup_id = int.Parse(result[0][11]);
            txlEntity.user_id = int.Parse(result[0][12]);

            return txlEntity;
        }

        public DataTable QueryForFuzzy(int user_id, string txl_name)
        {
            List<SqlParameter> parameterList = new List<SqlParameter>();

            parameterList.Add(new SqlParameter("@txl_name", "%" + txl_name + "%"));
            parameterList.Add(new SqlParameter("@user_id", user_id));
            string sql = "select txl_id 编号,txl_name 名称,txl_cellphone 手机,txl_telephone 固定电话,txl_address 联系地址,txl_email 邮箱,txl_QQ QQ,txl_fax 传真,txl_dept 部门, txl_duty 职位,txl_descn 备注,txl.txlgroup_id 小组编号,txlgroup_name 小组名称,txl.user_id 用户编号 " +
                " from user_txl txl,txl_group txlgroup where txl.txlgroup_id=txlgroup.txlgroup_id and txl_name like @txl_name and txl.user_id=@user_id";

            var result = DBExecuteUtil.querySqlTable(sql, parameterList.ToArray());
            return result;
        }

        public DataTable QueryEntityByGroupId(int txlgroup_id)
        {
            List<SqlParameter> parameterList = new List<SqlParameter>();
            parameterList.Add(new SqlParameter("@txlgroup_id", txlgroup_id));
            string sql = "select txl_id 编号,txl_name 名称,txl_cellphone 手机,txl_telephone 固定电话,txl_address 联系地址,txl_email 邮箱,txl_QQ QQ,txl_fax 传真,txl_dept 部门, txl_duty 职位,txl_descn 备注,txlgroup_id 小组编号,user_id 用户编号 " +
                " from user_txl where txlgroup_id=@txlgroup_id ";

            var dt = DBExecuteUtil.querySqlTable(sql, parameterList.ToArray());
            return dt;
        }

        public DataTable QueryByUserId(int user_id)
        {
            List<SqlParameter> parameterList = new List<SqlParameter>();
            parameterList.Add(new SqlParameter("@user_id", user_id));
            string sql = " select txl_id 编号,txl_name 名称,txl_cellphone 手机,txl_telephone 固定电话,txl_address 联系地址,txl_email Email,txl_QQ QQ,txl_fax 传真,txl_dept 部门, txl_duty 职位,txl_descn 备注,txl.txlgroup_id 小组编号,txlgroup_name 小组名称,txl.user_id 用户编号 " +
                             " from user_txl txl,txl_group txlgroup where txl.txlgroup_id=txlgroup.txlgroup_id and txl.user_id=@user_id ";

            var dt = DBExecuteUtil.querySqlTable(sql, parameterList.ToArray());
            return dt;
        }


        public DataTable QueryAll()
        {
            string sql = " select txl_id 编号,txl_name 名称,txl_cellphone 手机,txl_telephone 固定电话,txl_address 联系地址,txl_email Email,txl_QQ QQ,txl_fax 传真,txl_dept 部门, txl_duty 职位,txl_descn 备注,txl.txlgroup_id 小组编号,txlgroup_name 小组名称,txl.user_id 用户编号 " +
                             " from user_txl txl,txl_group txlgroup where txl.txlgroup_id=txlgroup.txlgroup_id  ";

            var dt = DBExecuteUtil.querySqlTable(sql);
            return dt;
        }
    }
}
